<?php if (!defined('TL_ROOT')) die('You can not access this file directly!');

/**
 * TYPOlight webCMS
 * Copyright (C) 2005-2009 Leo Feyer
 *
 * This program is free software: you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation, either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this program. If not, please visit the Free
 * Software Foundation website at http://www.gnu.org/licenses/.
 *
 * PHP version 5
 * @copyright  Leo Feyer 2005-2009
 * @author     Leo Feyer <leo@typolight.org>
 * @package    Newsletter
 * @license    LGPL
 * @filesource
 */


/**
 * Class OrderExport
 *
 * Provide methods to handle order.
 * @copyright  Leo Feyer 2005-2009
 * @author     Leo Feyer <leo@typolight.org>
 * @package    Controller
 */
class OrderExport extends Backend
{
	/**
	 * Get the order information by id.
	 * @param null
	 * @return null
	 */
	public function exportOrderById()
	{
		$arrOrders = array();
		//$this->import("Database");
		
		$objOrders = $this->Database->prepare(stripslashes("SELECT odr.`id`, odr.`orderno`, CONCAT(odr.`dly_firstname`, ' ', odr.`dly_lastname`, '(', member.`username`, ')') AS memberName, odr.`dly_cellphone`, odr.`dly_firstname`, odr.`dly_street`, odr.`dly_city`, odr.`dly_state`, odr.`dly_postcode`, odr.`dly_country`, odr.`dly_cellphone`, odr.`dly_cellphone`, odr.`payment`, odr.`total_discounts`, odr.`total_paid`, odr.`total_paid_real`, odr.`total_catolog`, odr.`total_shipping`, odr.`total_wrapping`, CASE odr.`odr_status` WHEN '1' THEN 'Payment accepted' WHEN '2' THEN 'Preparation in progress' WHEN '3' THEN 'Shipping' WHEN '6' THEN 'Cancelled'  ELSE 'Delivered' END AS orderStatus, cata.`skuid`, shippings.`name` AS shipping_method, odd.`product_name`, odd.`product_attribute`, odd.`product_quantity`, odd.`product_price`, odr.`date_add`, odr.`date_upd`, odr.`total_wrapping`, odr.`odr_comments`  
												FROM `tl_orders` AS odr
												LEFT JOIN `tl_order_detail` AS odd ON odr.`id`=odd.`pid` 
												LEFT JOIN `product_catalog` AS cata ON cata.`id`=odd.`product_catalog_id` 
												LEFT JOIN `tl_member` AS member ON member.`id`=odr.`id_member` 
												LEFT JOIN `tl_shippings` AS shippings ON shippings.id=odr.id_carrier
												ORDER BY odr.`id` DESC"))
									->execute(time(), time());
		//print_r($objOrders);exit;
		if ($objOrders->numRows < 1)
			return;
		
		$arrColumn[0] = array(
			'Order No.', 'Order Date & Time', 'Order Status', 'Name', 'Mobile Number', 'Product info', 'Shipping Address', 'Sub-total', 'Shipping Method', 'Shipping Costs', 'Discount', 'Price in Total', 'Gift Wrapping', 'Any comments'
		);
		
		$this->loadLanguageFile('countries');
		include(TL_ROOT . '/system/config/countries.php');
		
		// Get the states.
		$arrStates = array();
		$objStates = $this->Database->prepare("SELECT * FROM `china_province` ORDER BY `province_en`")
									->execute(time());
		
		while ($objStates->next())
		{
			$arrStates[$objStates->id] = stripslashes($objStates->province_en);
		}
		
		// Get State.
		$arrCitys = array();			
		$objCitys = $this->Database->prepare("SELECT * FROM `china_city` ORDER BY `city_en`")
									->execute(time());
									
		while ($objCitys->next())
		{
			$arrCitys[$objCitys->id] = stripslashes($objCitys->city_en);
		}
		
		$index = 0;
		while ($objOrders->next())
		{			
			$arrOrders[$index] = array(
				'orderno' => $objOrders->orderno,
				'date_add' => $objOrders->date_add,
				'orderStatus' => ($objOrders->payment == 'COD' && $objOrders->orderStatus == 'Payment accepted') ? 'Preparation in progress' : $objOrders->orderStatus,
				'memberName' => $objOrders->memberName,
				'mobile' => $objOrders->cellphone,
			);
			
			$arrAttributes = explode("\n", $objOrders->product_attribute);
			$arrColors = explode(":", $arrAttributes[0]);
			$arrSizes = explode(":", $arrAttributes[1]);
			
			$arrOrders[$index]['ProductInfo'] = $objOrders->skuid . "," . $objOrders->product_name . "," . $arrColors[1] . "," . $arrSizes[1] . "," . $objOrders->product_quantity;
			
			$arrOrders[$index]['shippingAddr'] = $objOrders->dly_firstname.' '.$objOrders->dly_lastname.'&#10;'.$objOrders->dly_street.'&#10;'.(($objOrders->dly_country=='cn'&&$arrCitys[$objOrders->dly_city]) ? $arrCitys[$objOrders->dly_city] : $objOrders->dly_city).'&#10;'.(($objOrders->dly_country=='cn'&&$arrStates[$objOrders->dly_state]) ? $arrStates[$objOrders->dly_state] : $objOrders->dly_state).' '.$objOrders->dly_postcode.' '.$objOrders->dly_country.'&#10;'.(($objOrders->dly_cellphone) ? 'Tel:'.$objOrders->dly_telephone.'&#10;' : '').(($objOrders->dly_cellphone)?'MP:'.$objOrders->dly_cellphone : '');		
			$arrOrders[$index]['subtotal'] = $objOrders->total_catolog;
			$arrOrders[$index]['shipping_method'] = $objOrders->shipping_method;
			$arrOrders[$index]['total_shipping'] = $objOrders->total_shipping;
			$arrOrders[$index]['total_discounts'] = $objOrders->total_discounts;
			$arrOrders[$index]['total_paid_real'] = $objOrders->total_paid_real;
			$arrOrders[$index]['total_wrapping'] = $objOrders->total_wrapping;
			if ($objOrders->odr_comments&&$objOrders->odr_comments=='FOR EXAMPLE: PLEASE DELIVER BETWEEN 7 AND 9PM IN THE EVENING')
				$arrOrders[$index]['odr_comments'] = '';
			else
				$arrOrders[$index]['odr_comments'] = $objOrders->odr_comments;
			
			$index++;								
		}
		
		//print_r($arrOrders);exit;
		$arrOrders = array_merge($arrColumn, $arrOrders);
		//print_r($arrOrders);exit;
		include_once(TL_ROOT."/plugins/phpexcel/excel-xml.php");
		$objExcel = Excel_XML_Generate::getInstance();
		//print_r($objExcel);exit;
		$objExcel->generateExcel('order_' . date("Y-m-d") . "_byid", $arrOrders);
	}
	
	/**
	 * Get the order information by skuid.
	 * @param null
	 * @return null
	 */
	public function exportOrderBySkuid()
	{
		$arrOrders = array();
		//$this->import("Database");
		
		$objOrders = $this->Database->prepare(stripslashes("SELECT odr.`id`, odr.`orderno`, CONCAT(odr.`dly_firstname`, ' ', odr.`dly_lastname`, '(', member.`username`, ')') AS memberName, odr.`dly_cellphone`, odr.`dly_firstname`, odr.`dly_street`, odr.`dly_city`, odr.`dly_state`, odr.`dly_postcode`, odr.`dly_country`, odr.`dly_cellphone`, odr.`dly_cellphone`, odr.`bill_firstname`, odr.`bill_lastname`, odr.`bill_street`, odr.`bill_street_2`, odr.`bill_postcode`, odr.`bill_city`, odr.`bill_state`, odr.`bill_country`, odr.`payment`, odr.`total_discounts`, odr.`total_paid`, odr.`total_paid_real`, odr.`total_catolog`, odr.`total_shipping`, odr.`total_wrapping`, CASE odr.`odr_status` WHEN '1' THEN 'Payment accepted' WHEN '2' THEN 'Preparation in progress' WHEN '3' THEN 'Shipping' WHEN '6' THEN 'Cancelled' ELSE 'Delivered' END AS orderStatus, cata.`skuid`, odd.`product_attribute`, odd.`product_quantity`, odr.`date_add`, odr.`date_upd`  
												FROM `tl_orders` AS odr
												LEFT JOIN `tl_order_detail` AS odd ON odr.`id`=odd.`pid` 
												LEFT JOIN `product_catalog` AS cata ON cata.`id`=odd.`product_catalog_id` 
												LEFT JOIN `tl_member` AS member ON member.`id`=odr.`id_member` 
												LEFT JOIN `tl_address_book` AS addr ON addr.`id`=odr.`id_address_delivery` 
												ORDER BY cata.`skuid`"))
									->execute(time(), time());
		//print_r($objOrders);exit;
		if ($objOrders->numRows < 1)
			return;
		
		$arrColumn[0] = array(
			'Catalog ID', 'Order Number', 'Member Name', 'Shipping Information', 'Invoice Information', 'Payment', 'Total discounts', 'Total Paid', 'Total Real Paid', 'Total Product Paid', 'Total Shipping Paid', 'Total Wrapping Paid', 'Order Status', 'Product Skuid', 'Product Attribute', 'Product Quantity', 'Order Created', 'Order Updated'
		);
		
		while ($objOrders->next())
		{
			$arrOrders[] = array(
				'id' => $objOrders->id,
				'orderno' => $objOrders->orderno,
				'memberName' => $objOrders->memberName,
				'shipping' => $objOrders->dly_firstname.' '.$objOrders->dly_lastname.'&#10;'.$objOrders->dly_street.'&#10;'.(($objOrders->dly_country=='cn'&&$arrCitys[$objOrders->dly_city]) ? $arrCitys[$objOrders->dly_city] : $objOrders->dly_city).'&#10;'.(($objOrders->dly_country=='cn'&&$arrStates[$objOrders->dly_state]) ? $arrStates[$objOrders->dly_state] : $objOrders->dly_state).' '.$objOrders->dly_postcode.' '.$objOrders->dly_country.'&#10;'.(($objOrders->dly_cellphone) ? 'Tel:'.$objOrders->dly_telephone.'&#10;' : '').(($objOrders->dly_cellphone)?'MP:'.$objOrders->dly_cellphone : ''),
				'invoiceInfo' => $objOrders->bill_firstname.' '.$objOrders->bill_lastname.'&#10;'.$objOrders->bill_street.'&#10;'.(($objOrders->bill_street_2)?$objOrders->bill_street_2.'&#10;':'').$objOrders->bill_postcode.' '.$objOrders->bill_city.' '.$objOrders->bill_state.' '.$objOrders->bill_country,
				'payment' => $objOrders->payment,
				'total_discounts' => $objOrders->total_discounts,
				'total_paid' => $objOrders->total_paid,
				'total_paid_real' => $objOrders->total_paid_real,
				'total_catolog' => $objOrders->total_catolog,
				'total_shipping' => $objOrders->total_shipping,	
				'total_wrapping' => $objOrders->total_wrapping,
				'orderStatus' => $objOrders->orderStatus,
				'skuid' => $objOrders->skuid,
				'product_attribute' => $objOrders->product_attribute,
				'product_quantity' => $objOrders->product_quantity,
				'date_add' => $objOrders->date_add,
				'date_upd' => $objOrders->date_upd,				
			);				
		}
		
		$arrOrders = array_merge($arrColumn, $arrOrders);
		//print_r($arrOrders);exit;
		include_once(TL_ROOT."/plugins/phpexcel/excel-xml.php");
		$objExcel = Excel_XML_Generate::getInstance();
		//print_r($objExcel);exit;
		$objExcel->generateExcel('order_' . date("Y-m-d") . "_byskuid", $arrOrders);
	}
}

?>